/**
 * @FileName: ProductService.java
 * @Author
 * @Description:
 * @Date 2016年4月20日 下午3:43:19
 * @CopyRight ZTE Corporation
 */
package cn.hpclub.server.service.product;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.hpclub.server.bean.OrgNode;
import cn.hpclub.server.bean.ProductBean;
import cn.hpclub.server.bean.ProductListBean;
import cn.hpclub.server.constant.ClientApiConstant;
import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.model.product.Product;
import cn.hpclub.server.model.product.ProductSku;
import cn.hpclub.server.model.product.SkuOpt;
import cn.hpclub.server.service.AccountService;
import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.QueryHelper;
import cn.hpclub.server.util.SystemConfigUtil;

public class ProductService{
    private static Logger              logger  = LoggerFactory.getLogger(ProductService.class);
    public static final ProductService service = new ProductService();
    public static final int            SIZE    = 10;
    private DecimalFormat              df      = new DecimalFormat("######0.00");

    /**
     * @Title: getQueryHelper
     * @Description:得到获取产品列表的QueryHelper
     * @param searchInfo
     * @return QueryHelper
     */

    public QueryHelper getQueryHelper(ConcurrentHashMap<String, String> searchInfo){

        String sqlOrgFilter = InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(searchInfo.get("org")),
                                                             Constant.MAX_ORG_LEVEL, true);

        // logger.debug("sqlOrgFilter:" + sqlOrgFilter);

        boolean isProductMgr = AccountService.service
                .getUserRoleType(InterfaceUtil.getAdminId()) == Constant.ROLE_TYPE_SUPPLIER_ADMIN ? true : false;
        logger.debug("isProductMgr:" + isProductMgr);

        QueryHelper helper = new QueryHelper(
                "select su.real_name,p.id,p.name,p.enabled,( case p.enabled when 1 then '启用' else '禁用' end ) as isEnable" +
                        "d,b.name as brand_name, (select sum(sk.sold) from product_sku sk where sk.product_id = p.id) as sold," +
                        "(select sum(sk.stock) from product_sku sk where sk.product_id = p.id) as stock," +
                        "p.put_on_time,p.put_off_time,p.status,o.DisplayName as orgName, sort,p.operated_time ",
                "product p left join brand b on p.brand_id=b.id left join organization o on p.org_id=o.id left join sys_user su on p.creator=su.id")//

                        // orgid的条件一定会存在.如果是商品管理员，则只能看自己账号下的数据。
                        .addCondition(isProductMgr, "p.creator = " + InterfaceUtil.getAdminId())

                        // 如果是其他账号，则能看自己机构和下级机构所有数据，包括管理员的数据和供应商的数据。
                        .addCondition(!isProductMgr, "p.org_id in " + sqlOrgFilter)

                        .addCondition(searchInfo.containsKey("name"), "p.name like ?",
                                      "%" + searchInfo.get("name") + "%")
                        .addCondition(searchInfo.containsKey("brand") && !"-1".equals(searchInfo.get("brand")),
                                      "p.brand_id=?", searchInfo.get("brand"))
                        .addCondition(searchInfo.containsKey("enabled") && !"-1".equals(searchInfo.get("enabled")),
                                      "p.enabled=?", searchInfo.get("enabled"))
                        .addOrderProperty("p.operated_time", false).addOrderProperty("p.sort", true);

        return helper;
    }
    /**
     * 
     * @param searchInfo
     * @return所有的商品，以product_sku为准，如果以后商品太多，请参照cn.hpclub.server.controller.admin.MemberOrgController#export这种循环导出
     */
    public List<Record> exportProducts(ConcurrentHashMap<String, String> searchInfo){
        boolean isProductMgr = AccountService.service
                .getUserRoleType(InterfaceUtil.getAdminId()) == Constant.ROLE_TYPE_SUPPLIER_ADMIN ? true : false;
        logger.debug("isProductMgr:" + isProductMgr);
        String sqlOrgFilter = InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(searchInfo.get("org")),
                                                             Constant.MAX_ORG_LEVEL, true);
        QueryHelper helper = new QueryHelper(
                "SELECT p.`name` AS product_name,p.put_on_time,p.put_off_time,( case p.enabled when 1 then '启用' else '禁用' end ) as isEnabled,p.`status`,p.is_visible,ps.cur_price,ps.model_code,brand.`name` AS brand_name,org.DisplayName AS org_name,su.real_name AS creator_name,(SELECT GROUP_CONCAT(so.`name`) FROM sku_opt so WHERE so.id IN("+
                        "SUBSTRING_INDEX(SUBSTRING_INDEX(ps.sku_opt_id,',',-4),',',1),"+
                        "SUBSTRING_INDEX(SUBSTRING_INDEX(ps.sku_opt_id,',',-3),',',1),"+
                        "SUBSTRING_INDEX(SUBSTRING_INDEX(ps.sku_opt_id,',',-2),',',1),"+
                        "SUBSTRING_INDEX(SUBSTRING_INDEX(ps.sku_opt_id,',',-1),',',1)"+
                        ")) as prop",//颜色 属性 内存  版本合在一起
                "product_sku ps LEFT JOIN product p ON ps.product_id=p.id LEFT JOIN brand ON p.brand_id=brand.id LEFT JOIN organization org ON org.id=p.org_id LEFT JOIN sys_user su ON su.id=p.creator")

                        // orgid的条件一定会存在.如果是商品管理员，则只能看自己账号下的数据。
                        .addCondition(isProductMgr, "p.creator = " + InterfaceUtil.getAdminId())

                        // 如果是其他账号，则能看自己机构和下级机构所有数据，包括管理员的数据和供应商的数据。
                        .addCondition(!isProductMgr, "p.org_id in " + sqlOrgFilter)

                        .addCondition(searchInfo.containsKey("name"), "p.name like ?",
                                      "%" + searchInfo.get("name") + "%")
                        .addCondition(searchInfo.containsKey("brand") && !"-1".equals(searchInfo.get("brand")),
                                      "p.brand_id=?", searchInfo.get("brand"))
                        .addCondition(searchInfo.containsKey("enabled") && !"-1".equals(searchInfo.get("enabled")),
                                      "p.enabled=?", searchInfo.get("enabled"))
                        .addOrderProperty("p.operated_time", false).addOrderProperty("p.sort", true);
        List<Record> records = Db.find(helper.getSelect()+helper.getSqlExceptSelect());
        return records;
    }
    /**
     * @Title: getProductById
     * @Description:获取商品详情，注：该接口客户端和后台都在使用，需要改动请慎重!!!
     * @param parseInt
     * @return Product
     */
    public Record getProductById(int id){
        String sql = "select * from product where is_visible=1 and id=?";
        Record product = Db.findFirst(sql, id);
        // 设置分类信息
        // setProductCategory(product);

        // category = getCategory(category.getInt(Category.PARENT_ID));
        //
        // if(category != null && category.getInt(Category.PARENT_ID) != 0){
        // cateSql = "select c.name,c.parent_id from category c where
        // c.enabled=1 and c.parent_id=?";
        // Category.dao.findFirstCategory(cateSql,
        // product.getInt(Product.CATE_ID));
        // }

        // 获取商品sku信息
        sql = "select product_id,sku_opt_id,barcode,cur_price,profit,refer_price,stock,model_code from product_sku where enabled=1 and product_id=?";
        List<Record> ptSku = Db.find(sql, product.getInt(Product.ID));

        // List<Record> ptSku =
        // ProductSkuService.service.getProductSkuByProductId(product.getInt(Product.ID));
        // 设置商品sku信息
        setProductSku(ptSku, product);

        // 设置商品sku_opt信息
        setProductSkuOpt(ptSku, product);

        return product;
    }

    public List<Object> getProductListByMember(int org_id, int member_type, int page, String key, int sort,
            String order){

        List<Object> result = new LinkedList<Object>();
        List<Record> list = null;
        ProductListBean bean = null;
        StringBuffer sb = new StringBuffer("from product where name like ?");
        if(member_type != Constant.MemberType.MANAGER){ // 店员
            sb.append(" and org_id in " + InterfaceUtil.getParentOrgIdSql(org_id, Constant.MAX_ORG_LEVEL));
        }
        switch(sort){
            case 1:
                sb.append(" order by sales ");
                break;
            case 2:
                sb.append(" order by profit ");
                break;
            case 3:
                sb.append(" order by price ");
                break;
            case 4:
                sb.append(" order by stock ");
                break;
            default:
                sb.append(" order by id ");
                break;
        }
        sb.append(order);
        list = Db.paginate(page, SIZE, "select id,name,title,thumbnail,profit", sb.toString(), "%" + key + "%")
                .getList();

        for(Record record : list){
            bean = new ProductListBean();
            bean.id = String.valueOf(record.getInt("id"));
            bean.name = record.getStr("name");
            bean.title = record.getStr("title");
            bean.price = "0.00";
            bean.profit = String.valueOf(record.getDouble("profit"));
            bean.stock = "0";
            bean.thumbnail = SystemConfigUtil.getSystemConfig().getHostPrefix() + record.getStr("thumbnail");
            // System.out.println("thumbnail=" + bean.thumbnail);
            result.add(bean);
        }

        return result;
    }

    /*
     * private void setProductCategory(Record product){ String cateSql = "";
     * Category category = null; String catePath = ""; String seperator = " -> "
     * ;
     * 
     * int cateId = product.getInt(Product.CATE_ID); while(true){ cateSql =
     * "select name,parent_id,level from category where enabled=1 and id=?";
     * category = Category.dao.findFirst(cateSql, cateId);
     * 
     * if(category != null){ cateId = category.getInt(Category.PARENT_ID);
     * 
     * catePath = category.getStr(Category.NAME) + seperator + catePath;
     * 
     * if(cateId <= 0 || category.getInt(Category.LEVEL) <= 1){ break; } } else{
     * break; } } if(StrKit.notBlank(catePath)){ int targetIndex =
     * catePath.length() - seperator.length(); product.set("cate_info",
     * catePath.substring(0, targetIndex)); } else{ product.set("cate_info",
     * catePath); } }
     */

    private void setProductSkuOpt(List<Record> ptSku, Record product){
        JSONObject skuOpts = new JSONObject();
        JSONObject skuOpt = null;

        for(Record r : ptSku){
            skuOpt = new JSONObject();
            skuOpt.put("productid", r.getInt(ProductSku.PRODUCT_ID));
            skuOpt.put("sku_opt_id", r.getStr(ProductSku.SKU_OPT_ID));
            skuOpt.put("barcode", r.getStr(ProductSku.BARCODE));
            skuOpt.put("cur_price", r.getDouble(ProductSku.CUR_PRICE));
            skuOpt.put("profit", r.getDouble(ProductSku.PROFIT));
            skuOpt.put("refer_price", r.getDouble(ProductSku.REFER_PRICE));
            skuOpt.put("stock", r.getInt(ProductSku.STOCK));
            skuOpt.put("model_code", r.getStr(ProductSku.MODEL_CODE));

            skuOpts.put(r.getStr(ProductSku.SKU_OPT_ID), skuOpt);

            // TODO product_sku_opt_id排序
        }

        product.set("sku_opts", skuOpts);
    }

    private void setProductSku(List<Record> ptSku, Record product){
        String[] skuOptIdArr = null;
        SkuOpt skuOpt = null;
        JSONArray skuArray = new JSONArray();
        JSONArray skuOptArray = null;
        JSONObject skuObj = null;
        JSONObject skuOptObj = null;
        ConcurrentHashMap<Integer, JSONObject> skuMap = new ConcurrentHashMap<Integer, JSONObject>();
        ConcurrentHashMap<Integer, JSONObject> skuOptMap = new ConcurrentHashMap<Integer, JSONObject>();
        String sql = "";
        Record pso = null;

        for(Record r : ptSku){
            if(StrKit.notBlank(r.getStr(ProductSku.SKU_OPT_ID)) && !"0".equals(r.getStr(ProductSku.SKU_OPT_ID))){
                skuOptIdArr = r.getStr(ProductSku.SKU_OPT_ID).split(",");

                for(String soId : skuOptIdArr){
                    sql = "select so.id,so.name,pso.sku_img as image,pso.remark,s.id as skuid,s.name as skuname,s.sort from sku_opt so left join sku s on s.id=so.sku_id left join product_sku_opt pso on pso.sku_opt_id=so.id where so.enabled=1 and s.enabled=1 and so.id=?";
                    skuOpt = SkuOpt.dao.findFirst(sql, soId);

                    // prodcut_sku
                    skuObj = skuMap.get(skuOpt.getInt("skuid"));
                    if(skuObj == null){
                        skuObj = new JSONObject();
                        skuObj.put("skuid", skuOpt.getInt("skuid"));
                        skuObj.put("skuname", skuOpt.getStr("skuname"));
                        skuObj.put("sort", skuOpt.getInt("sort"));
                    }

                    skuOptArray = (JSONArray)skuObj.get("opt");
                    if(skuOptArray == null){
                        skuOptArray = new JSONArray();
                    }

                    // sku、sku_opt
                    skuOptObj = skuOptMap.get(skuOpt.getInt(SkuOpt.ID));
                    if(skuOptObj == null){
                        // product_sku_opt
                        sql = "select sku_img as image,remark from product_sku_opt pso where sku_opt_id=? and product_id=?";
                        pso = Db.findFirst(sql, new Object[] { skuOpt.getInt(SkuOpt.ID), product.getInt(Product.ID) });

                        skuOptObj = new JSONObject();

                        skuOptObj.put("id", skuOpt.getInt(SkuOpt.ID));
                        skuOptObj.put("name", skuOpt.getStr(SkuOpt.NAME));
                        if(pso != null){
                            skuOptObj.put("image", pso.getStr("image") == null ? "" : pso.getStr("image"));
                            skuOptObj.put("remark", pso.getStr("remark") == null ? "" : pso.getStr("remark"));
                        } else{
                            skuOptObj.put("image", "");
                            skuOptObj.put("remark", "");
                        }

                        skuOptMap.put(skuOpt.getInt(SkuOpt.ID), skuOptObj);

                        skuOptArray.add(skuOptObj);

                        skuObj.put("opt", skuOptArray);

                        skuMap.put(skuOpt.getInt("skuid"), skuObj);
                    }

                }
            }
        }

        // 通过ArrayList构造函数把map.entrySet()转换成list
        List<ConcurrentHashMap.Entry<Integer, JSONObject>> mappingList = new ArrayList<ConcurrentHashMap.Entry<Integer, JSONObject>>(
                skuMap.entrySet());
        // 通过比较器实现比较排序
        Collections.sort(mappingList, new Comparator<ConcurrentHashMap.Entry<Integer, JSONObject>>(){
            public int compare(ConcurrentHashMap.Entry<Integer, JSONObject> map1,
                    ConcurrentHashMap.Entry<Integer, JSONObject> map2){

                int sort1 = map1.getValue().getIntValue("sort");
                int sort2 = map2.getValue().getIntValue("sort");
                // logger.debug("sort1 = " + sort1);
                // logger.debug("sort2 = " + sort2);
                return sort1 - sort2;
            }
        });

        // 遍历skuMap，取出数据到skuArray中
        for(Entry<Integer, JSONObject> item : mappingList){
            JSONObject value = item.getValue();
            if(value != null){
                skuArray.add(value);
            }
        }

        product.set("sku", skuArray);
    }

    // private String getCategoryByParentId(int cateParentId){
    // String cateSql = "select c.name,c.parent_id from category c where
    // c.enabled=1 and c.id=?";
    // Category category = Category.dao.findFirst(cateSql, cateParentId);
    //
    // if(category != null && category.getInt(Category.PARENT_ID) != 0){
    // category = getCategoryByParentId(category.getInt(Category.PARENT_ID));
    // }
    //
    // return "";
    // }

    /**
     * @Title: changeStatus
     * @Description:改变上下架状态
     * @param id
     * @param targetStatus
     * @return boolean
     */

    public boolean changeStatus(String id, String targetStatus){
        String sql = "";
        Date date = new Date();
        if(targetStatus.equalsIgnoreCase("1")){
            sql = "update product set status = 1,put_on_time=? where id=?";
        } else{
            sql = "update product set status = 0,put_off_time=? where id=?";
        }

        return Db.update(sql, date, id) > 0;
    }

    public Object queryProductById(int id){
        ProductBean bean = new ProductBean();
        Record product = Db.findFirst("select * from product where is_visible=1 and id=?", id);
        // 获取商品sku信息
        List<Record> ptSku = Db.find(
                                     "select product_id,sku_opt_id,barcode,cur_price,profit,refer_price,stock from product_sku where enabled=1 and product_id=?",
                                     product.getInt(Product.ID));
        // 设置商品sku信息
        setProductSku(ptSku, product);

        // 设置商品sku_opt信息
        setProductSkuOpt(ptSku, product);

        bean.id = String.valueOf(product.getInt("id"));
        bean.name = product.getStr("name");
        bean.title = product.getStr("title");
        bean.price = "0.00";
        bean.stock = String.valueOf(product.getInt("stock"));
        bean.brand = "";
        bean.category = "";
        bean.profit = String.valueOf(product.getDouble("profit"));
        bean.thumbnail = product.getStr("thumbnail");
        bean.banner_img = product.getStr("banner_img");
        bean.desp_summary = product.getStr("desp_summary");
        bean.desp_aftersale = product.getStr("desp_aftersale");
        bean.desp_detail = product.getStr("desp_detail");
        bean.visit = String.valueOf(product.getInt("visit"));
        bean.sku = product.get("sku");
        bean.sku_opts = product.get("sku_opts");
        // 获取商品sku信息

        return bean;
    }

    /**
     * @Title: setStatus
     * @Description:设置商品的上下架状态，维护上下架时间
     * @param product
     * @param isEdit
     * @param status
     */

    public void setStatus(Product product, boolean isEdit, Integer status){
        if(product == null){
            return;
        }
        Date now = new Date();
        if(isEdit){// 编辑状态
            String productId = product.get(Product.ID);
            if(null == productId){// 说明在获取到Id之前调用了
                return;
            }
            // 获取原来的状态
            Integer statusOld = Product.dao.findFirst("select p.status from product p where p.id=?", productId)
                    .getInt(Product.STATUS);
            if(statusOld == 1 && status == 0){// 上架->下架
                product.set(Product.STATUS, status);
                product.set(Product.PUT_OFF_TIME, now);
            } else if(statusOld == 0 && status == 1){// 下架->上架
                product.set(Product.STATUS, status);
                product.set(Product.PUT_ON_TIME, now);
            }
        } else{// 新增
            product.set(Product.STATUS, status);
            if(status == 1){// 上架
                product.set(Product.PUT_ON_TIME, now);
            }
        }
    }

    /**
     * @Title: saveRegionInfo
     * @Description:TODO
     * @param productId
     * @param regionId
     *            void
     */
    public void saveRegionInfo(String productId, int regionId){
        /* 删除原地域信息 */
        Db.update("DELETE FROM product_region WHERE product_id=" + productId);
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("INSERT INTO product_region(product_id, region_id) VALUES (?,?)");
        Db.update(sbSql.toString(), productId, regionId);
    }

    public List<CheckBoxBean> getRegionListForEdit(String productId){
        List<CheckBoxBean> regionList = getRegionList();
        /* 标记与商品关联的区域 */
        List<Record> productRegionList = Db.find("SELECT * FROM product_region WHERE product_id=" + productId);
        for(CheckBoxBean regionBean : regionList){
            for(int i = 0; i < productRegionList.size(); i++){
                if(productRegionList.get(i).getInt("region_id") == regionBean.key){
                    regionBean.setChecked(true);
                    break;
                }
            }
        }
        return regionList;
    }

    public List<CheckBoxBean> getRegionList(){
        List<CheckBoxBean> list = new ArrayList<CheckBoxBean>();

        OrgNode root = InterfaceUtil.getOrgTree(InterfaceUtil.getIdTop(), 1, false);
        if(root == null || root.getSubNode() == null || root.getSubNode().size() == 0){
            return list;
        }
        for(OrgNode node : root.getSubNode()){
            list.add(new CheckBoxBean(false, node.getId(), node.getDisplayName()));
        }
        return list;
    }

    public static class CheckBoxBean{
        private boolean checked;
        private int     key;
        private String  value;

        public CheckBoxBean(boolean checked, int key, String value){
            this.checked = checked;
            this.key = key;
            this.value = value;
        }

        public boolean isChecked(){
            return checked;
        }

        public void setChecked(boolean checked){
            this.checked = checked;
        }

        public int getKey(){
            return key;
        }

        public void setKey(int key){
            this.key = key;
        }

        public String getValue(){
            return value;
        }

        public void setValue(String value){
            this.value = value;
        }
    }

    /**
     * @Title: getQueryProductListHelper
     * @Description:TODO
     * @param searchInfo
     * @return QueryHelper
     */
    public QueryHelper getQueryProductListHelper(ConcurrentHashMap<String, String> searchInfo){
        String dateFormat = "%Y%m%d%H%M%S";

        QueryHelper helper = new QueryHelper(
                "select p.id,p.name,p.title,p.brand_id,p.thumbnail,left(p.banner_img,(length(p.banner_img)-1)) as banner_img,p.desp_summary,p.put_on_time,p.put_off_time,p.org_id,p.creator,p.create_time,concat(min(ps.cur_price)) as price",
                "product p left join product_region pr on pr.product_id=p.id left join product_sku ps on ps.product_id=p.id")//
                        .addCondition(searchInfo.containsKey(ClientApiConstant.Key.ORGID),
                                      "((p.put_off_time is not null and ((date_format(now(),'" + dateFormat
                                              + "') >= date_format(p.put_on_time,'" + dateFormat
                                              + "')) and (date_format(now(),'" + dateFormat
                                              + "') < date_format(p.put_off_time,'" + dateFormat
                                              + "')))) or (p.put_off_time is null and (date_format(now(),'" + dateFormat
                                              + "') >= date_format(p.put_on_time,'" + dateFormat
                                              + "')))) and pr.region_id=(select o.CityCode from organization o where o.id=?) and ps.model_code is not null and ps.model_code!=''",
                                      Integer.parseInt(searchInfo.get(ClientApiConstant.Key.ORGID)))
                        .addCondition(searchInfo.containsKey(ClientApiConstant.Key.KEYWORDS), "p.name like ?",
                                      "%" + searchInfo.get(ClientApiConstant.Key.KEYWORDS) + "%")
                        .addGroupProperty("ps.product_id")
                        .addOrderProperty("0".equals(searchInfo.get(ClientApiConstant.Key.SEQUENCE))
                                && "1".equals(searchInfo.get(ClientApiConstant.Key.SORT)), "p.put_on_time", false)
                        .addOrderProperty("0".equals(searchInfo.get(ClientApiConstant.Key.SEQUENCE))
                                && "2".equals(searchInfo.get(ClientApiConstant.Key.SORT)), "price", false)
                        .addOrderProperty("1".equals(searchInfo.get(ClientApiConstant.Key.SEQUENCE))
                                && "1".equals(searchInfo.get(ClientApiConstant.Key.SORT)), "p.put_on_time", true)
                        .addOrderProperty("1".equals(searchInfo.get(ClientApiConstant.Key.SEQUENCE))
                                && "2".equals(searchInfo.get(ClientApiConstant.Key.SORT)), "price", true);

        return helper;
    }

    /**
     * @param searchInfo
     * @Title: queryProductList
     * @Description:TODO
     * @param helper
     * @return Page<Record>
     */
    public Page<Record> queryProductList(ConcurrentHashMap<String, String> searchInfo, QueryHelper helper){
        logger.debug("**************queryProductList*************");
        logger.debug("select = " + helper.getSelect());
        logger.debug("sqlExceptSelectByGroup = " + helper.getSqlExceptSelectByGroup());
        logger.debug("parameters = " + helper.getParameters());

        return Db.paginate(Integer.parseInt(searchInfo.get(ClientApiConstant.Key.PAGE_INDEX)),
                           Integer.parseInt(searchInfo.get(ClientApiConstant.Key.PAGE_SIZE)), helper.getSelect(),
                           helper.getSqlExceptSelectByGroup(), helper.getParameters().toArray());
    }

    /**
     * @Title: getProductByIdClient
     * @Description:TODO
     * @param parseInt
     * @return Record
     */
    public Record getProductByIdClient(int productid){
        String sql = "select p.id,p.name,p.title,p.brand_id,p.thumbnail,left(p.banner_img,(length(p.banner_img)-1)) as banner_img,p.desp_summary,p.desp_detail,p.put_on_time,p.put_off_time,p.org_id,p.creator,p.create_time,p.operated_by,p.operated_time,p.operated_desc,min(ps.cur_price) as minprice,max(ps.cur_price) as maxprice from product p left join product_sku ps on ps.product_id=p.id where p.id=? and ps.model_code is not null and ps.model_code!='' group by ps.product_id";
        Record product = Db.findFirst(sql, productid);

        if(product == null)
            return null;

        // 获取商品sku信息
        sql = "select product_id,sku_opt_id,cur_price,model_code from product_sku where product_id=? and model_code is not null and model_code!=''";
        List<Record> ptSku = Db.find(sql, product.getInt(Product.ID));

        // 设置商品sku信息
        setProductSku(ptSku, product);

        // 设置商品sku_opt信息
        setProductSkuOptClient(ptSku, product);

        // 设置价格
        setProductPrice(product);

        return product;
    }

    /**
     * @Title: setProductSkuOptClient
     * @Description:TODO
     * @param ptSku
     * @param product
     *            void
     */
    private void setProductSkuOptClient(List<Record> ptSku, Record product){
        JSONObject skuOpts = new JSONObject();
        JSONObject skuOpt = null;
        String skuOptIds = "";

        for(Record r : ptSku){
            // product_sku_opt_id排序
            skuOptIds = getSkuOptIdsAfterSort(r.getStr(ProductSku.SKU_OPT_ID));

            skuOpt = new JSONObject();
            skuOpt.put("productid", r.getInt(ProductSku.PRODUCT_ID));
            skuOpt.put("sku_opt_id", StrKit.isBlank(skuOptIds) ? r.getStr(ProductSku.SKU_OPT_ID) : skuOptIds);
            skuOpt.put("cur_price", df.format(r.getDouble(ProductSku.CUR_PRICE).doubleValue()));
            skuOpt.put("model_code", r.getStr(ProductSku.MODEL_CODE));

            skuOpts.put(skuOptIds, skuOpt);

        }

        product.set("sku_opts", skuOpts);
    }

    /**
     * @Title: getSkuOptIdsAfterSort
     * @Description:TODO
     * @param str
     * @return String
     */
    private String getSkuOptIdsAfterSort(String skuOptIds){
        StringBuffer curSkuOptIds = new StringBuffer();
        String[] skuOptIdArr = null;
        String temp = "";
        if(StrKit.notBlank(skuOptIds)){
            skuOptIdArr = skuOptIds.split(",");
            Arrays.sort(skuOptIdArr);

            for(String optid : skuOptIdArr){
                curSkuOptIds.append(optid).append(",");
            }

            temp = curSkuOptIds.toString().substring(0, curSkuOptIds.toString().length() - 1);
        }

        return StrKit.isBlank(temp) ? skuOptIds : temp;
    }

    /**
     * @Title: setProductPrice
     * @Description:TODO
     * @param product
     *            void
     */
    private void setProductPrice(Record product){
        // 设置价格区间
        if(product.getDouble("minprice").doubleValue() == product.getDouble("maxprice").doubleValue()){
            product.set("price", df.format(product.getDouble("minprice").doubleValue()));
        } else{
            product.set("price", df.format(product.getDouble("minprice").doubleValue()) + "－"
                    + df.format(product.getDouble("maxprice").doubleValue()));
        }

        product.set("minprice", df.format(product.getDouble("minprice").doubleValue()));
        product.set("maxprice", df.format(product.getDouble("maxprice").doubleValue()));
    }

    /**
     * @Title: getProductListHelper
     * @Description:TODO
     * @param searchInfo
     * @param isAll
     * @param host
     * @return QueryHelper
     */
    public QueryHelper getProductListHelper(ConcurrentHashMap<String, String> searchInfo, boolean isAll,
            int productType){
        String dateFormat = "%Y%m%d%H%i%S";

        String params = null;
        QueryHelper helper = new QueryHelper(
                "select p.id,p.name,p.title,p.brand_id,p.thumbnail,left(p.banner_img,(length(p.banner_img)-1)) as banner_img,p.desp_summary,p.put_on_time,p.put_off_time,p.org_id,p.creator,p.create_time,p.sort,p.attr_type,concat(min(ps.cur_price)) as price,sum(ps.sold) as sold",
                "product p left join product_region pr on pr.product_id=p.id left join product_sku ps on ps.product_id=p.id")//
                        .addCondition(true, "p.enabled=1 and ((p.put_off_time is not null and ((date_format(now(),'"
                                + dateFormat + "') >= date_format(p.put_on_time,'" + dateFormat
                                + "')) and (date_format(now(),'" + dateFormat + "') < date_format(p.put_off_time,'"
                                + dateFormat + "')))) or (p.put_off_time is null and (date_format(now(),'" + dateFormat
                                + "') >= date_format(p.put_on_time,'" + dateFormat
                                + "')))) and ps.model_code is not null and ps.model_code!=''", params)// 只获取已上架且没下架，且机型码不为空的商品
                        .addCondition(productType == ClientApiConstant.ProductType.ALL
                                && searchInfo.containsKey(ClientApiConstant.Key.GROUPID),
                                      "(pr.region_id=(select o.CityCode from organization o where o.NumCode=?) or pr.region_id=0)",
                                      searchInfo.get(ClientApiConstant.Key.GROUPID))// 搜索所有商品（包括省级和地市级）
                        .addCondition(productType == ClientApiConstant.ProductType.RECOMMEND, "pr.region_id=?", 0)// 只获取省级商品
                        .addCondition(productType == ClientApiConstant.ProductType.HOST
                                && searchInfo.containsKey(ClientApiConstant.Key.GROUPID),
                                      "pr.region_id=(select o.CityCode from organization o where o.NumCode=?)",
                                      searchInfo.get(ClientApiConstant.Key.GROUPID))// 只获取地市级商品
                        .addCondition(searchInfo.containsKey(ClientApiConstant.Key.KEYWORDS), "p.name like ?",
                                      "%" + searchInfo.get(ClientApiConstant.Key.KEYWORDS) + "%");

        if(isAll){// 获取全部商品需要加上价格销量排序和过滤
            // 过滤合约机 OR 裸机
            helper.addCondition(searchInfo.containsKey(ClientApiConstant.Key.FILTER)
                    && !ClientApiConstant.FilterType.ALL.equals(searchInfo.get(ClientApiConstant.Key.FILTER)),
                                "(p.attr_type=? or p.attr_type=3)", searchInfo.get(ClientApiConstant.Key.FILTER))
                    // 默认按排序字段升序+销量降序排序
                    .addOrderProperty(ClientApiConstant.SortType.DEFAULT
                            .equals(searchInfo.get(ClientApiConstant.Key.SORT)), "p.sort", true)// 按排序字段升序
                    .addOrderProperty(ClientApiConstant.SortType.DEFAULT
                            .equals(searchInfo.get(ClientApiConstant.Key.SORT)), "sum(ps.sold)", false)// 按销量降序
                    // 按价格、销量排序
                    .addOrderProperty(ClientApiConstant.SortType.PRICE
                            .equals(searchInfo.get(ClientApiConstant.Key.SORT))
                            && ClientApiConstant.SequenceType.DESC
                                    .equals(searchInfo.get(ClientApiConstant.Key.SEQUENCE)), "min(ps.cur_price)", false)// 按价格降序
                    .addOrderProperty(ClientApiConstant.SortType.PRICE
                            .equals(searchInfo.get(ClientApiConstant.Key.SORT))
                            && ClientApiConstant.SequenceType.ASC
                                    .equals(searchInfo.get(ClientApiConstant.Key.SEQUENCE)), "min(ps.cur_price)", true)// 按价格升序
                    .addOrderProperty(ClientApiConstant.SortType.SOLD.equals(searchInfo.get(ClientApiConstant.Key.SORT))
                            && ClientApiConstant.SequenceType.DESC
                                    .equals(searchInfo.get(ClientApiConstant.Key.SEQUENCE)), "sum(ps.sold)", false)// 按销量降序
                    .addOrderProperty(ClientApiConstant.SortType.SOLD.equals(searchInfo.get(ClientApiConstant.Key.SORT))
                            && ClientApiConstant.SequenceType.ASC
                                    .equals(searchInfo.get(ClientApiConstant.Key.SEQUENCE)), "sum(ps.sold)", true);// 按销量升序
        } else{// 分级获取商品只需要默认排序
               // 默认按排序字段升序+销量降序排序
            helper.addOrderProperty(ClientApiConstant.SortType.DEFAULT
                    .equals(searchInfo.get(ClientApiConstant.Key.SORT)), "p.sort", true)
                    .addOrderProperty(ClientApiConstant.SortType.DEFAULT
                            .equals(searchInfo.get(ClientApiConstant.Key.SORT)), "sum(ps.sold)", false);
        }

        helper.addGroupProperty("ps.product_id");

        return helper;
    }

}
